/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: Wangyaoxi
 * Date: 2010-4-7 上午09:08:16
 */
package com.ccxe.manage.util.help;

import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import com.ccxe.manage.db.CcxDB;
import com.ccxe.manage.db.DBFactory;
import com.ccxe.manage.service.page.bean.PageBean;

/**
 * dao辅助类
 * 
 * @author wyx
 * 
 */
public class DaoHelper {
	private EntityHelper eHelper = new EntityHelper();
	private SqlHelper sqlHelper = new SqlHelper();

	private CcxDB db;

	/**
	 * wyx:由sql获取list<map>集合
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map<?, ?>> getListBySql(String sql) throws Exception {

		return getListBySql(sql, null);
	}

	/**
	 * wyx:由预声明sql和参数集合获得list<map>
	 * 
	 * @param sql
	 * @param prams
	 * @return
	 * @throws Exception
	 */
	public List<Map<?, ?>> getListBySql(String sql, Object[] prams)
			throws Exception {

		ResultSet rs = null;
		List<Map<?, ?>> result = null;
		try {
			this.initDb();
			// 得到rs
			rs = db.executeQuery(sql, prams);
			// 得到list<map>
			result = eHelper.getListInRs(rs);
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				rs.close();
			}
			db.close();
		}
		return result;
	}

	/**
	 * wyx:由select count 语句获得数据条数
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public int getCount(String sql) throws Exception {

		return getCount(sql, null);
	}

	/**
	 * wyx:由 预声明sql和参数数组获得 count
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public int getCount(String sql, Object[] obj) throws Exception {

		ResultSet rs = null;
		Long result = 0L;

		try {
			this.initDb();
			// 获取rs
			rs = db.executeQuery(sql, obj);
			while (rs.next()) {
				// 得到返回
				result = rs.getLong(1);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				rs.close();
			}
			db.close();
		}
		return result.intValue();
	}

	/**
	 * wyx：获取rs, connection,stmt未关闭,调用时需关闭
	 * 
	 * @param sql
	 * @return
	 */
	public ResultSet getRsBySql(String sql) {

		ResultSet rs = null;
		try {
			this.initDb();
			rs = db.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * wyx：获取分页数据
	 * 
	 * @param pb
	 *            分页bean
	 * @param sql
	 *            sql语句
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<Map<?, ?>> getListByPage(PageBean pb, String sql,
			Object[] params) throws Exception {
		// count sql
		String countSql = sqlHelper.buildPageCountSql(sql);
		// 获取count
		Integer count = this.getCount(countSql, params);
		// 更新pagebean属性
		pb.setTotalData(count);
		pb.setTotalPage(pb.computeTotalPage());
		// 取当前页码为 总页码与前台传值 二者最小值
		pb.setNowPage(Math.min(pb.getNowPage(), pb.getTotalPage()));
		String pageSql = sqlHelper.buildPageSql(sql, pb);
		// 得到返回
		return this.getListBySql(pageSql, params);
	}

	// 转换一条结果集数据
	public Map<?, ?> getOneInrs(ResultSet rs) {
		Map<?, ?> obj = eHelper.getOneInRs(rs);
		return obj;
	}

	/**
	 * 获取CcxDb,来关闭
	 * 
	 * @return
	 */
	public CcxDB getDb() {
		return db;
	}

	/**
	 * wyx:获取数据库中表头
	 * 
	 * @param tableName
	 * @return
	 */
	public ResultSet getColumnInTable(String tableName) {
		String sql = sqlHelper.buildTableColumnSql(tableName);

		ResultSet rs = null;
		try {
			this.initDb();
			rs = db.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * wyx:由 预声明sql和参数数组获得 count
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public int insertOrUpdateOrDelete(String sql, Object[] obj)
			throws Exception {

		int result = 0;

		try {
			this.initDb();
			// 获取rs
			result = db.executeUpdate(sql, obj);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return result;
	}

	public void setDb(CcxDB db) {
		this.db = db;
	}

	public void initDb() throws Exception {
		db = DBFactory.getInstance();
	}

}
